package server;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Savepoint;
import java.sql.Statement;

//to run this code, download MySQL Connector/J 5.0 from http://dev.mysql.com/downloads/connector/j/5.0.html
//extract the binary .jar from the archive, and add it to your java classpath !
//a nice link i found: http://www.kitebird.com/articles/jdbc.html

//after installing MYSQL server, create a database named "firewall":
//mysql -u root -p
//press ENTER
//type "create database firewall;"
//type "quit"

//compile this code "javac MYSQLComm.java" (and Rule.java)
//run it "java -classpath .:mysql-connector-java-5.0.4-bin.jar MYSQLComm"

//TO THINK

//should this support multi-threading to an instance ?

//where i do rs.next() might give ok, while empty... try to check it or use getSize.

//should enums be caps ? will mysql accept it in enum ?

//escape chars, how to handle

//DONE
//do we need a finer-grain control over exceptions (both inside failure for close, and outside
//for fallback or critical ?). for internal best wrap all with finally .close statments ?
//do we need to escape chars in strings (especially '?)
//how do i know how much does conn.rollback() delete ? (especially with GC on previous calls ? set a savepoint?)
//string .tocmpare, not ==

public class MYSQLComm {

	static boolean mysqlLoaded = false;
	String hostname;
	String dbname;
	boolean persistent;
	String username;
	String password;
	Connection conn = null;

	Savepoint savept = null;
	
	public MYSQLComm(String hostname, String dbname, String username, String password, boolean persistent) throws SQLException, Exception {
		if (mysqlLoaded == false)
			try {
				// does this need to be a singleton? or per connection
				Class.forName("com.mysql.jdbc.Driver").newInstance();
				mysqlLoaded = true;
			} catch (Exception e) {
				mysqlLoaded = false;
				throw e;
			}
			this.hostname = hostname;
			this.dbname = dbname;
			this.persistent = persistent;
			this.username = username;
			this.password = password;
			if (persistent)
				connect();
	}

	private void connect() throws SQLException
	{
		if (conn == null || conn.isClosed() == true)
		{
			conn = DriverManager.getConnection("jdbc:mysql://" + hostname + "/" + dbname, username, password);
		}
	}

	private void disconnect() throws SQLException
	{
		if (conn != null && persistent == false)
		{
			conn.close();
			conn = null;
		}
	}

	protected void finalize() throws Throwable {
		try {
			if (conn != null) conn.close();
		} finally {
			super.finalize();
		}
	}

	private int getSize(ResultSet rs) throws SQLException
	{
		rs.last();
		int size = rs.getRow();
		rs.beforeFirst();
		return size;
	}

	public void addRule(String table, Rule rule) throws SQLException
	{
		connect();
		Statement s = conn.createStatement();
		Savepoint save = null;
		try {
			conn.setAutoCommit(false);
			save = conn.setSavepoint();
			s.executeUpdate("UPDATE `" + table + "` SET `index` = `index` + 1 WHERE `index` >= " + rule.index + ";");
			if (rule.type == Rule.typeOpt.CONTEXT)
			{
    			String port;
    			if (rule.port == -1) port = "NULL"; else port = new Integer(rule.port).toString();
				s.executeUpdate("INSERT INTO `" + table + "` (`index`, `type`, `port`, `protocol`, `permission`) VALUES " + "(" + rule.index + ", '" + rule.type + "', " + port + ", '" + rule.protocol +"', '" + rule.permission +"');");
			}
			else
			{
    			String port;
    			if (rule.port == -1) port = "NULL"; else port = new Integer(rule.port).toString();
    			String iprange = (rule.iprange == null || rule.iprange.equals("")) ? "NULL" : "'" + rule.iprange + "'";
				s.executeUpdate("INSERT INTO `" + table + "` (`index`, `type`, `ipRange`, `port`, `permission`) VALUES " + "(" + rule.index  + ", '" + rule.type + "', " + iprange + ", " + port + ", '" + rule.permission + "');");
			}
			conn.commit();
		} catch (SQLException e) {
			conn.rollback(save);
			throw e;
		} finally {
			conn.setAutoCommit(true);
			s.close();
			disconnect();
		}
	}

	public void deleteRule(String table, int index) throws SQLException
	{
		connect();
		Statement s = conn.createStatement();
		Savepoint save = null;
		try {
			conn.setAutoCommit(false);
			savept = save = conn.setSavepoint();
			s.executeUpdate("DELETE FROM `" + table + "` WHERE `index` = " + index + ";");
			s.executeUpdate("UPDATE `" + table + "` SET `index` = `index` - 1 WHERE `index` > " + index + ";");
		} catch (SQLException e) {
			//conn.rollback(save);
			throw e;
		} finally {
//			conn.setAutoCommit(true);
			s.close();
			disconnect();
		}
	}

	public void deleteRuleComplete(boolean complete) throws SQLException
	{
	    try {
    		if (complete)
    		{
    			conn.commit();
    		}
    		else
    		{
    			conn.rollback(savept);
       		}
   		} catch (SQLException e) {
    			conn.rollback(savept);
   		} finally {
    		conn.setAutoCommit(true);
        }
	}
	
	public Rule[] getRules(String table) throws SQLException
	{
		connect();
		Statement s = conn.createStatement();
		try {
			s.executeQuery("SELECT `index`, `type`, `iprange`, `port`, `protocol`, `permission` FROM `" + table + "` ORDER BY `index`;");
			ResultSet rs = s.getResultSet();
			int size = getSize(rs);
			Rule[] results = new Rule[size];
			int count = 0;
			while (rs.next())
			{
				Rule rule = new Rule();
				rule.index = rs.getInt("index");
				rule.type = (rs.getString("type").compareToIgnoreCase("IP") == 0) ? Rule.typeOpt.IP : Rule.typeOpt.CONTEXT;
				rule.iprange = rs.getString("iprange");
				rule.port = rs.getInt("port");
				rule.protocol = rs.getString("protocol");
				String perm = rs.getString("permission");
				if (perm == null)
				{
				    rule.permission = null;
				}
				else
				{
				    if (perm.compareToIgnoreCase("ALLOW") == 0)
				        rule.permission = Rule.permissionOpt.ALLOW;
				    else
				        rule.permission = Rule.permissionOpt.DENY;
				}
				results[count] = rule;
				++count;
			}
			rs.close();
			return results;
		} finally {
			s.close();
			disconnect();

		}
	}

	public Password getPassword(String user) throws SQLException
	{
		connect();
		Statement s = conn.createStatement();
		try {
			s.executeQuery("SELECT `user`, `password`, `type` FROM `Passwords` WHERE `user` = '" + user + "'");
			ResultSet rs = s.getResultSet();
			if (rs.next() == false)
				throw new SQLException("No such user");
			Password ret = new Password();
			ret.user = rs.getString("user");
			ret.password = rs.getString("password");
			ret.type = (rs.getString("type").compareToIgnoreCase("SIMPLE") == 0) ? Password.typeOpt.SIMPLE : Password.typeOpt.EXPERT;
			rs.close();
			return ret;
		} finally {
			s.close();
			disconnect();
		}

	}

	public void addProtocol(Protocol protocol) throws SQLException
	{
		connect();
		Statement s = conn.createStatement();
		try {
			String outReg = protocol.outReg == null ? "NULL" : "'" + protocol.outReg + "'";
			String inReg = protocol.inReg == null ? "NULL" : "'" + protocol.inReg + "'";
			s.executeUpdate("INSERT INTO `Protocols` (`protocol`, `outReg`, `inReg`) VALUES ('" + protocol.protocol + "', " + outReg + ", " + inReg + ");");
		} finally {
			s.close();
			disconnect();
		}
	}

	public void deleteProtocol(String name) throws SQLException
	{
		connect();
		Statement s = conn.createStatement();
		try {
			s.executeUpdate("DELETE FROM `Protocols` WHERE `protocol` = '" + name + "';");
		} finally {
			s.close();
			disconnect();
		}
	}
	
	public void updateProtocol(Protocol protocol) throws SQLException
	{
		connect();
		Statement s = conn.createStatement();
		try {
			String outReg = protocol.outReg == null ? "NULL" : "'" + protocol.outReg + "'";
			String inReg = protocol.inReg == null ? "NULL" : "'" + protocol.inReg + "'";
			s.executeUpdate("UPDATE `Protocols` SET `outReg` = " + outReg + ", `inReg` = " + inReg + " WHERE `protocol` = '" + protocol.protocol + "';");
		} finally {
			s.close();
			disconnect();
		}
	}

	public Protocol[] getProtocols() throws SQLException
	{
		connect();
		Statement s = conn.createStatement();
		try {
			s.executeQuery("SELECT `protocol`, `outReg`, `inReg` FROM `Protocols`;");
			ResultSet rs = s.getResultSet();
			int size = getSize(rs);
			Protocol[] results = new Protocol[size];
			int count = 0;
			while (rs.next())
			{
				Protocol protocol = new Protocol();
				protocol.inReg = rs.getString("inReg");
				protocol.outReg = rs.getString("outReg");
				protocol.protocol = rs.getString("protocol");
				results[count] = protocol;
				++count;
			}
			rs.close();
			return results;
		} finally {
			s.close();
			disconnect();
		}
	}

	public Protocol getProtocol(String name) throws SQLException
	{
		connect();
		Statement s = conn.createStatement();
		try {
			s.executeQuery("SELECT `protocol`, `outReg`, `inReg` FROM `Protocols` WHERE `protocol` = '" + name + "' ORDER BY `protocol`;");
			ResultSet rs = s.getResultSet();
			if (rs.next() == false)
				throw new SQLException("No such protocol");
			Protocol protocol = new Protocol();
			protocol.inReg = rs.getString("inReg");
			protocol.outReg = rs.getString("outReg");
			protocol.protocol = rs.getString("protocol");
			rs.close();
			return protocol;
		} finally {
			s.close();
			disconnect();
		}

	}

	public void createRuleSet(String name) throws SQLException
	{
		connect();		
		Statement s = conn.createStatement();
		try {
//			System.out.println("SHOW TABLES LIKE `RuleSet_" + name + "_In`;");
//			s.executeQuery("SHOW TABLES LIKE `RuleSet_" + name + "_In`;");
//			ResultSet rs = s.getResultSet();
//			if (rs.next())
//				throw new SQLException("Table already exists");
			s.executeUpdate("CREATE TABLE RuleSet_" + name + "_In SELECT * FROM `ActiveRules_In`;");
			s.executeUpdate("CREATE TABLE RuleSet_" + name + "_Out SELECT * FROM `ActiveRules_Out`;");
//			s.executeUpdate("CREATE TABLE RuleSet_" + name + "_In LIKE `ActiveRules_In`;");
//			s.executeUpdate("CREATE TABLE RuleSet_" + name + "_Out LIKE `ActiveRules_Out`;");
//			rs.close();
		} finally {
			s.close();
			disconnect();
		}
	}

	public void deleteRuleSet(String name) throws SQLException
	{
		connect();
		Statement s = conn.createStatement();
		try {
			s.executeUpdate("DROP TABLE IF EXISTS `RuleSet_" + name + "_In`;");
			s.executeUpdate("DROP TABLE IF EXISTS `RuleSet_" + name + "_Out`;");
		} finally {
			s.close();
			disconnect();
		}
	}

	public String[] getRuleSets() throws SQLException
	{
		connect();
		Statement s = conn.createStatement();
		try {
			s.executeQuery("SHOW TABLES LIKE `RuleSet_%_In`;");
			ResultSet rs = s.getResultSet();
			int size = getSize(rs);
			String[] results = new String[size];
			int count = 0;
			while (rs.next())
			{
				String name = rs.getString("Tables_in_" + dbname);
				name = name.split("_")[1];
				results[count] = name;
				++count;
			}
			rs.close();
			return results;
		} finally {
			s.close();
			disconnect();
		}
	}

	//add log abilites
	//-1 all
	public Log[] getLogs(int days) throws SQLException
	{
		connect();
		Statement s = conn.createStatement();
		try {
			//this works ?
			s.executeQuery("SELECT UNIX_TIMESTAMP(`time`), `direct`, `sourceIp`, `destIp`, `sourcePort`, `destPort`, `protocol`, `rulePort`, `ruleProtocol`, `action` FROM `Log` WHERE (DATEDIFF(CURRENT_DATE, `time`) < " + days + ") ORDER BY `time`;");
			ResultSet rs = s.getResultSet();
			int size = getSize(rs);
			Log[] results = new Log[size];
			int count = 0;
			while (rs.next())
			{
				Log log = new Log();
				log.time = rs.getLong("UNIX_TIMESTAMP(`time`)");
				log.direct = (rs.getString("direct").compareToIgnoreCase("IN") == 0) ? Log.directOpt.IN : Log.directOpt.OUT;
				log.sourceIp = rs.getString("sourceIp");
				log.destIp = rs.getString("destIp");
				log.sourcePort = rs.getInt("sourcePort");
				log.destPort = rs.getInt("destPort");
				log.protocol = rs.getString("protocol");
				log.rulePort = rs.getInt("rulePort");
				log.ruleProtocol= rs.getString("ruleProtocol");
				log.action = (rs.getString("action").compareToIgnoreCase("ACCEPT")) == 0 ? Log.actionOpt.ACCEPT : Log.actionOpt.DENY;
				results[count] = log;
				++count;
			}
			rs.close();
			return results;
		} finally {
			s.close();
			disconnect();
		}
	}

	public GlobalLog[] getGlobalLogs(int days) throws SQLException
	{
		connect();
		Statement s = conn.createStatement();
		try {
			//this works ?
			s.executeQuery("SELECT UNIX_TIMESTAMP(`time`), `event` FROM `GlobalLog` WHERE (DATEDIFF(CURRENT_DATE, `time`) < " + days + ") ORDER BY `time`;");
			ResultSet rs = s.getResultSet();
			int size = getSize(rs);
			GlobalLog[] results = new GlobalLog[size];
			int count = 0;
			while (rs.next())
			{
				GlobalLog log = new GlobalLog();
				log.time = rs.getLong("UNIX_TIMESTAMP(`time`)");
				log.event = rs.getString("event");
				results[count] = log;
				++count;
			}
			rs.close();
			return results;
		} finally {
			s.close();
			disconnect();
		}
	}

	public void addGlobalLog(String error) throws SQLException
	{
		connect();
		Statement s = conn.createStatement();
		try {
		    error.replace('\'', ' ');
		    error.replace('`', ' ');
			s.executeUpdate("INSERT INTO `GlobalLog` (`time`, `event`) VALUES (NOW(), '" + error + "');");
		} finally {
			s.close();
			disconnect();
		}
	}

	public static void main(String[] args) {
		//Date a = new Date();
		MYSQLComm mysql = null;
		try {
			mysql = new MYSQLComm("192.168.0.31", "FW", "root", "", true);
		} catch (SQLException e)
		{
			System.err.println ("Error message: " + e.getMessage());
			System.err.println ("Error number: " + e.getErrorCode());
			return;
		} catch (Exception e) {
			e.printStackTrace();
		}
		/*		try {
			mysql.initializeDB();
		} catch (SQLException e)
		{
			if (e.getErrorCode() != 1050)
			{
				System.err.println ("Error message: " + e.getMessage());
				System.err.println ("Error number: " + e.getErrorCode());
				return;
			}
		}
		try {
		} catch (SQLException e)
		{
			System.err.println ("Error message: " + e.getMessage());
			System.err.println ("Error number: " + e.getErrorCode());
			return;
		}*/
		try {
			Rule[] rules = mysql.getRules("ActiveRules_In");
			System.out.println("Showing " + rules.length + " rules:");
			for (Rule r : rules)
			{
				System.out.println("Name = " + r.index);
			}
		} catch (SQLException e)
		{
			System.err.println ("Error message: " + e.getMessage());
			System.err.println ("Error number: " + e.getErrorCode());
			return;
		}
	}
}
